﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Domain.NetworkService;
using System.Net.NetworkInformation;
using System.Net;
using Domain.NetworkRemoteDevice;
using System.Net.Sockets;

namespace Domain.Entities
{
    public class DBWorker
    {
        string constr = System.Configuration.
                                ConfigurationManager.
                                ConnectionStrings
                                ["Domain.Properties.Settings.NetworkMonitorConnectionString"]
                                .ConnectionString;
        string dateFormat = "yyyy-MM-ddTHH:mm:ss";
        
        public void WriteTestResult(SnmpResult result, string deviceName)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[InsertSNMPResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@ip", result.IP.ToString()));
                com.Parameters.Add(new SqlParameter("@date", result.VarDateTime.ToString(dateFormat)));
                com.Parameters.Add(new SqlParameter("@variable", result.Variable.ToString()));
                com.Parameters.Add(new SqlParameter("@status", result.Status.ToString()));
                com.Parameters.Add(new SqlParameter("@data", result.Data.ToString()));
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                con.Open();
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0} {1}", e.Message, result.VarDateTime.ToString(dateFormat));
                }
            }
        }
        public void WriteTestResult(ResolveResult result, string deviceName)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                if (result.Ips != null)
                {
                    foreach (var ip in result.Ips)
                    {
                        SqlCommand com = new SqlCommand("[InsertResolveResult]", con);
                        com.CommandType = CommandType.StoredProcedure;
                        com.Parameters.Add(new SqlParameter("@hostname", result.HostName));
                        com.Parameters.Add(new SqlParameter("@ip", ip.ToString()));
                        com.Parameters.Add(new SqlParameter("@date", result.VarDateTime.ToString(dateFormat)));
                        com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                        con.Open();
                        try
                        {
                            com.ExecuteNonQuery();
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("{0} {1}", e.Message, result.VarDateTime.ToString(dateFormat));
                        }
                        con.Close();
                    }
                }
                else
                {
                    SqlCommand com = new SqlCommand("[InsertResolveResult]", con);
                    com.CommandType = CommandType.StoredProcedure;
                    com.Parameters.Add(new SqlParameter("@hostname", result.HostName));
                    com.Parameters.Add(new SqlParameter("@ip", result.HostName.ToString()));
                    com.Parameters.Add(new SqlParameter("@date", result.VarDateTime.ToString(dateFormat)));
                    com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                    con.Open();
                    try
                    {
                        com.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("{0} {1}", e.Message, result.VarDateTime.ToString(dateFormat));
                    }
                }
            }
        }
        public void WriteTestResult(TcpResult result, string deviceName)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[InsertTCPResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@ip", result.IP.ToString()));
                com.Parameters.Add(new SqlParameter("@port", (int)result.Port));
                com.Parameters.Add(new SqlParameter("@status", result.Status.ToString()));
                com.Parameters.Add(new SqlParameter("@info", result.Info.ToString()));
                com.Parameters.Add(new SqlParameter("@date", result.VarDateTime.ToString(dateFormat)));
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                con.Open();
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0} {1}", e.Message, result.VarDateTime.ToString(dateFormat));
                }
            }
        }
        public void WriteTestResult(EchoResult result, string deviceName)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[InsertEchoResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@ip", result.IP.ToString()));
                com.Parameters.Add(new SqlParameter("@status", result.Status.ToString()));
                com.Parameters.Add(new SqlParameter("@rtt", result.Rtt));
                com.Parameters.Add(new SqlParameter("@date", result.VarDateTime.ToString(dateFormat)));
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                con.Open();
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0} {1}", e.Message, result.VarDateTime.ToString(dateFormat));
                }
            }
        }
        public void WriteLog(string msg, MessageType msgType)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[InsertLog]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@msgType", msgType.ToString()));
                com.Parameters.Add(new SqlParameter("@msgContent", msg));
                com.Parameters.Add(new SqlParameter("@date", DateTime.Now.ToString(dateFormat)));
                con.Open();
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0}", e.Message);
                }
            }
        }

        public ResolveResult getLastResolveResult(string deviceName)
        {
            ResolveResult rr = new ResolveResult();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getResolveResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    rr.HostName = reader["hostname"].ToString();

                    IPAddress tmpIP;
                    if (IPAddress.TryParse((reader["ip"].ToString()), out tmpIP))
                    {
                        rr.Ips.Add(tmpIP);
                    }
                    else
                    {
                        rr.Ips.Add(IPAddress.Parse("0.0.0.0"));
                        WriteLog(String.Format("{0}. Не распознан IP адрес.", deviceName),
                            MessageType.Error);
                    }

                    DateTime tempDateTime;
                    if (DateTime.TryParse(reader["date"].ToString(), out tempDateTime))
                    {
                        rr.VarDateTime = tempDateTime;
                    }
                    else
                    {
                        rr.VarDateTime = DateTime.Now;
                    }
                }
            }
            if (rr.VarDateTime > DateTime.Parse("1753-1-1T12:00:00") &&
                rr.VarDateTime < DateTime.Parse("9999-12-30T12:00:00"))
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    SqlCommand com = new SqlCommand("[getResolveResultIPs]", con);
                    com.CommandType = CommandType.StoredProcedure;
                    com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                    com.Parameters.Add(new SqlParameter("@date", rr.VarDateTime));
                    con.Open();
                    SqlDataReader reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        IPAddress tmpIP;
                        if (IPAddress.TryParse(reader["ip"].ToString(), out tmpIP))
                        {
                            rr.Ips.Add(tmpIP);
                        }
                        else
                        {
                            rr.Ips.Add(IPAddress.Parse("0.0.0.0"));
                            WriteLog(String.Format("{0}. Не распознан IP адрес.", deviceName),
                                MessageType.Error);
                        }
                    }
                } 
            }
            return rr;
        }
        public EchoResult getLastEchoResult(string deviceName)
        {
            EchoResult er = new EchoResult();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getEchoResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    IPAddress tmpIP;
                    if (IPAddress.TryParse((reader["ip"].ToString()), out tmpIP))
                    {
                        er.IP = tmpIP;
                    }
                    else
                    {
                        er.IP = IPAddress.Parse("0.0.0.0");
                        WriteLog(String.Format("{0}. Не распознан IP адрес.", deviceName),
                            MessageType.Error);
                    }
                    er.Rtt = (int)reader["rtt"];

                    IPStatus tmpIPstatus;
                    if (Enum.TryParse<IPStatus>(reader["status"].ToString(), true, out tmpIPstatus))
                    {
                        er.Status = tmpIPstatus;
                    }
                    else
                    {
                        er.Status = IPStatus.Unknown;
                    }
                    DateTime tempDateTime;
                    if (DateTime.TryParse(reader["date"].ToString(), out tempDateTime))
                    {
                        er.VarDateTime = tempDateTime;
                    }
                    else
                    {
                        er.VarDateTime = DateTime.Now;
                    }
                    
                }
            }
            return er;
        }
        public NetworkTestResult getLastSnmpResult(string deviceName, Enum variable)
        {
            SnmpResult sr = new SnmpResult();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getSnmpResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                com.Parameters.Add(new SqlParameter("@variable", variable.ToString()));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    IPAddress tmpIP;
                    if (IPAddress.TryParse((reader["ip"].ToString()), out tmpIP))
                    {
                        sr.IP = tmpIP;
                    }
                    else
                    {
                        sr.IP = IPAddress.Parse("0.0.0.0");
                        WriteLog(String.Format("{0}. Не распознан IP адрес.", deviceName),
                            MessageType.Error);
                    }
                    
                    OidNames tmpOidName;
                    if (Enum.TryParse<OidNames>(reader["variable"].ToString(), true, out tmpOidName))
                    {
                        sr.Variable = tmpOidName;
                    }
                    else
                    {
                        //sr.Variable = OidNames.Unknown;
                        sr.Variable = OidNames.Hostname;
                    }

                    ResultStatus tmpResultStatus;
                    if (Enum.TryParse<ResultStatus>(reader["status"].ToString(), true, out tmpResultStatus))
                    {
                        sr.Status = tmpResultStatus;
                    }
                    else
                    {
                        sr.Status = ResultStatus.Failed;
                    }
                    sr.Data = reader["data"].ToString();
                    DateTime tempDateTime;
                    if (DateTime.TryParse(reader["date"].ToString(), out tempDateTime))
                    {
                        sr.VarDateTime = tempDateTime;
                    }
                    else
                    {
                        sr.VarDateTime = DateTime.Now;
                    }
                }
            }
            return sr;
        }
        public NetworkTestResult getLastTCPResult(string deviceName, Enum port)
        {
            TcpResult tr = new TcpResult();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getTcpResult]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                TcpPorts tcpPort = (TcpPorts)port;
                com.Parameters.Add(new SqlParameter("@port", tcpPort));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    IPAddress tmpIP;
                    if (IPAddress.TryParse((reader["ip"].ToString()), out tmpIP))
                    {
                        tr.IP = tmpIP;
                    }
                    else
                    {
                        tr.IP = IPAddress.Parse("0.0.0.0");
                        WriteLog(String.Format("{0}. Не распознан IP адрес.", deviceName),
                            MessageType.Error);
                    }

                    TcpPorts tmpTcpPort;
                    if (Enum.TryParse<TcpPorts>(reader["port"].ToString(), true, out tmpTcpPort))
                    {
                        tr.Port = tmpTcpPort;
                    }
                    else
                    {
                        tr.Port = TcpPorts.ECHO_tcp;
                    }

                    ResultStatus tmpResultStatus;
                    if (Enum.TryParse<ResultStatus>(reader["status"].ToString(), true, out tmpResultStatus))
                    {
                        tr.Status = tmpResultStatus;
                    }
                    else
                    {
                        tr.Status = ResultStatus.Failed;
                    }
                    SocketError tmpSocketError;
                    if (Enum.TryParse<SocketError>(reader["info"].ToString(), true, out tmpSocketError))
                    {
                        tr.Info = tmpSocketError;
                    }
                    DateTime tempDateTime;
                    if (DateTime.TryParse(reader["date"].ToString(), out tempDateTime))
                    {
                        tr.VarDateTime = tempDateTime;
                    }
                    else
                    {
                        tr.VarDateTime = DateTime.Now;
                    }
                }
            }
            return tr;
        }
        public List<DataGraphic> getEchoResultInDateRange(string deviceName, DateTime startDate, DateTime endDate)
        {
            List<DataGraphic> result = new List<DataGraphic>();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getEchoResultInDateRange]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@deviceName", deviceName));
                com.Parameters.Add(new SqlParameter("@startDate", startDate));
                com.Parameters.Add(new SqlParameter("@endDate", endDate));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    int rtt = Int32.Parse(reader["rtt"].ToString());
                    int year = DateTime.Parse(reader["date"].ToString()).Year;
                    int month = DateTime.Parse(reader["date"].ToString()).Month;
                    int day = DateTime.Parse(reader["date"].ToString()).Day;
                    int hour = DateTime.Parse(reader["date"].ToString()).Hour;
                    int minute = DateTime.Parse(reader["date"].ToString()).Minute;
                    int second = DateTime.Parse(reader["date"].ToString()).Second;
                    result.Add(new DataGraphic 
                    {
                        result = rtt,
                        year = year,
                        month = month,
                        day =day,
                        hour = hour,
                        minute = minute,
                        second = second
                    });
                }
            }
            return result;
        }

        public int[] getMinMaxAvg(string devName, DateTime dateTime, DateTime dateTime_2)
        {
            var result = new int[4];
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getMinMaxAvg]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@deviceName", devName));
                com.Parameters.Add(new SqlParameter("@startDate", dateTime));
                com.Parameters.Add(new SqlParameter("@endDate", dateTime_2));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        try
                        {
                            result[0] = int.Parse(reader["maxRtt"].ToString());
                            result[1] = int.Parse(reader["minRtt"].ToString());
                            result[2] = int.Parse(reader["avgRtt"].ToString());
                            result[3] = int.Parse(reader["lost"].ToString());
                        }
                        catch (Exception)
                        {  }  
                    }
                }
            }
            return result;
        }

        public List<ErrorLogs> getErrors(int count = 5)
        {
            var result = new List<ErrorLogs>();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getErrors]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@count", count));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    string type = reader["Type"].ToString();
                    DateTime date = DateTime.Parse(reader["date"].ToString());
                    string deviceName = reader["deviceName"].ToString();
                    string ip = reader["ip"].ToString();
                    string infos = reader["infos"].ToString();
                    result.Add(new ErrorLogs { Type = type, Date = date, DeviceName = deviceName, IP = ip,  Infos = infos});
                }
            }
            return result;
        }

        public List<ErrorLogs> getErrorsOfDevice(string devName, double daysAgo)
        {
            var result = new List<ErrorLogs>();
            DateTime endDate = DateTime.Now;
            DateTime startDate = DateTime.Now.AddDays(daysAgo);

            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand com = new SqlCommand("[getErrorsOfDevice]", con);
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.Add(new SqlParameter("@devName", devName));
                com.Parameters.Add(new SqlParameter("@startDate", startDate));
                com.Parameters.Add(new SqlParameter("@endDate", endDate));
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    string type = reader["Type"].ToString();
                    DateTime date = DateTime.Parse(reader["date"].ToString());
                    string deviceName = reader["deviceName"].ToString();
                    string ip = reader["ip"].ToString();
                    string infos = reader["infos"].ToString();
                    result.Add(new ErrorLogs { Type = type, Date = date, DeviceName = deviceName, IP = ip, Infos = infos });
                }
            }
            return result;
        }
    }
}
